﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;
using Data.ViewModel;

namespace DataAccess.BikeManage
{
    /// <summary>
    /// 停车点管理
    /// </summary>
    public class LockAddress : DbBase, ILockAddress
    {
        /// <summary>
        /// 通过id获取停车点详细信息
        /// </summary>
        /// <param name="id">停车点id</param>
        /// <returns></returns>
        public ResultInfo<lockaddress> GetLockAddress(int id)
        {
            var result = new ResultInfo<lockaddress> { ResultNumber = ResultEnum.Error };
            try
            {
                var b = new lockaddress();
                if (id > 0)
                {
                    var sql = string.Format("select * from lockaddress where id={0}", id);
                    var ds = ExcuteSqlDataSet(sql);
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        b = new lockaddress(ds.Tables[0].Rows[0]);

                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "不存在该停车点";
                    }
                    result.Obj = b;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "查询停车点失败";
                }

            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;

        }

        /// <summary>
        /// 获取低于最少停车数量的停车点
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="threshold">警戒值</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<lockaddress>> GetWarnLockAddress(int pageIndex, int pageSize, int threshold)
        {
            var result = new ResultInfo<PagingDataSet<lockaddress>>();
            if (threshold > 0)
            {
                try
                {
                    using (SqlCommand myCommand = GetProCommand())
                    {
                        myCommand.CommandText = "Common_GetPagingRecords";
                        var fromClause = new StringBuilder();
                        fromClause.Append("lockaddress with (nolock) ");
                        var whereClause = new StringBuilder();
                        whereClause.Append(string.Format("threshold <= {0}", threshold));

                        myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                        myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                        myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                        myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                        myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "id";
                        myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                        myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                        myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                        var dataSet = new DataSet();
                        var da = new SqlDataAdapter(myCommand);
                        da.Fill(dataSet);

                        var list = dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new lockaddress(dataRow)).Where(temp => temp.TranByConstructFlag).ToList();

                        int totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                        bool reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                        if (reverseOrder)
                            list.Reverse();

                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = new PagingDataSet<lockaddress> { PageIndex = pageIndex, PageSize = pageSize, Records = list, TotalRecords = totalRecords };
                    }
                }
                catch (Exception e)
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = e.Message;
                }
            }
            else
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "阀值不能小于0";
            }
            return result;
        }

        /// <summary>
        /// 获取停车点列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="areaid">区域id</param>
        /// <param name="addressName">停车点名称</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<LockAddressView>> GetLockAddress(int pageIndex, int pageSize, int areaid, string addressName)
        {
            var result = new ResultInfo<PagingDataSet<LockAddressView>>();
            try
            {
                using (SqlCommand myCommand = GetProCommand())
                {
                    myCommand.CommandText = "Common_GetPagingRecords";
                    var fromClause = new StringBuilder();
                    fromClause.Append("lockaddress with (nolock) ");
                    var whereClause = new StringBuilder();
                    if (!string.IsNullOrEmpty(addressName))
                    {
                        whereClause.Append(string.Format(" and name like '%{0}%' ", addressName));
                    }
                    if (areaid > 0)
                    {
                        whereClause.Append(string.Format(" and areaid ={0} ", areaid));
                    }
                    if (whereClause.Length > 3)
                    {
                        whereClause.Remove(0, 5);
                    }

                    myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                    myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                    myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                    myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                    myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                    myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "id";
                    myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                    myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                    var list = new List<lockaddress>();

                    var dataSet = new DataSet();
                    var da = new SqlDataAdapter(myCommand);
                    da.Fill(dataSet);

                    list.AddRange(dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new lockaddress(dataRow)).Where(temp => temp.TranByConstructFlag));

                    int totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                    bool reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                    if (reverseOrder)
                        list.Reverse();
                    var listView = PopulateLockAddress(list);

                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = new PagingDataSet<LockAddressView> { PageIndex = pageIndex, PageSize = pageSize, Records = listView, TotalRecords = totalRecords };
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 修改停车点的阀值
        /// </summary>
        /// <param name="id">停车点id</param>
        /// <param name="threshold">阀值大小</param>
        /// <returns></returns>
        public bool SetLockAddressThreshold(int id, int threshold)
        {
            bool result = false;
            string sql = string.Format("UPDATE lockaddress SET [threshold]={1} where [id]={0}", id, threshold);

            int temp = ExcuteSql(sql);
            if (temp > 0)
                result = true;
            //CloseConn();
            return result;
        }

        /// <summary>
        /// 创建或者是更新一个停车点的信息
        /// </summary>
        /// <param name="address">停车点信息，如果是有id大于0的，就是更新，如果小于等于0就是创建</param>
        /// <returns></returns>
        public ResultInfo<int> CreateOrUpdateLockAddress(lockaddress address)
        {
            var result = new ResultInfo<int>();
            try
            {
                string sql = string.Empty;
                if (address != null)
                {
                    if (address.id > 0)
                    {
                        sql = string.Format("UPDATE lockaddress SET [name]='{0}',[areaid]={1},[areaname]='{2}',[status]='{3}',[threshold]={4} where [id]={5} select {5} id", address.name, address.areaid, address.areaname, address.status, address.threshold, address.id);
                    }
                    else
                    {
                        string check = string.Format("select id from lockaddress where name='{0}'", address.name);
                        DataSet dt = ExcuteSqlDataSet(check);
                        if (dt != null && dt.Tables[0].Rows.Count > 0)
                        {
                            result.ResultNumber = ResultEnum.Error;
                            result.Describe = "已经存在同名停车区域，请重新输入";
                        }
                        else
                        {
                            sql = string.Format("insert into lockaddress([name],[areaid],[areaname],[status],[threshold]) values('{0}',{1},'{2}','{3}',{4}) select @@identity id", address.name, address.areaid, address.areaname, address.status, address.threshold);
                        }
                    }
                    if (!string.IsNullOrEmpty(sql))
                    {
                        DataSet dataSet = ExcuteSqlDataSet(sql);
                        if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
                        {
                            int temp = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                            if (temp > 0)
                            {
                                result.ResultNumber = ResultEnum.Success;
                                result.Obj = temp;
                            }
                            else
                            {
                                result.ResultNumber = ResultEnum.Error;
                                result.Describe = "更新数据失败";
                            }
                        }
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "请输入有效数据";
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 删除停车点根据id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultInfo<bool> DeleteLockAddress(int id)
        {
            var result = new ResultInfo<bool>();
            try
            {
                string sql = string.Format("delete from lockaddress where id={0}", id);
                int temp = ExcuteSql(sql);
                if (temp > 0)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = true;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Obj = false;
                }
            }
            catch (Exception)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "删除失败";
            }
            return result;
        }

        private static List<LockAddressView> PopulateLockAddress(IEnumerable<lockaddress> address)
        {
            int i = 0;
            var addList = new List<LockAddressView>();
            foreach (lockaddress add in address)
            {
                i++;
                addList.Add(new LockAddressView
                {
                    Serial = i,
                    Id = add.id,
                    Name = add.name,
                    AreaId = add.areaid,
                    AreaName = add.areaname,
                    Status = add.status,
                    Threshold = add.threshold
                });
            }
            return addList;
        }



    }
}
